﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using QLBH.ConfigLib;
using System.Data.SqlClient;
using System.IO;
using QLBH.StandardUI;
using System.Threading;
using System.Text.RegularExpressions;
using PluginFramework.Common;
using QLBH.Model.Controller;

namespace Module.SystemUI
{    
    public partial class FrmDBConfiguration : Form
    {
        private QLBHSettings setting;
        private ProgressDlg importProgressDialog;
        private Thread thread;
        private bool authen = true;
        private String ServerName;
        private String UserName;
        private String Password;
        public FrmDBConfiguration()
        {
            InitializeComponent();
            this.KeyPreview = true;
        }

        private void FrmConfiguration_Load(object sender, EventArgs e)
        {
            setting = QLBHSettings.Instance;

            tbxServerName.Text = setting.ServerName;
            tbxUserName.Text = setting.UserName;
            tbxPassword.Text = setting.PassWord;
            tbxDatabaseFolder.Text = setting.DbFileDirectory;

            cmbAuthentication.SelectedIndex = setting.Authen == true ? 0 : 1;

            lbStatus.Text = String.Empty;
            lbStatus.ForeColor = Color.Blue;
        }

        private void btnTestConnection_Click(object sender, EventArgs e)
        {
            SqlConnection connection;
            try
            {                
                String connectionstring = EntityDBConnectionHelper.Instance.GetMasterConnectionString(
                    authen, tbxServerName.Text, tbxUserName.Text, tbxPassword.Text);
                connection = new SqlConnection(connectionstring);

                Cursor = Cursors.WaitCursor;
                connection.Open();
            }
            catch (System.Exception)
            {
                lbStatus.Text = "Kết nối tới " + tbxServerName.Text + " không thành công.";
                lbStatus.ForeColor = Color.Red;
                btnCreateDatabase.Enabled = false;
                Cursor = Cursors.Default;
                return;
            }
            try
            {
                lbStatus.Text = "Kết nối tới " + tbxServerName.Text + " thành công";
                lbStatus.ForeColor = Color.Blue;
                
                connection.Close();
                btnCreateDatabase.Enabled = true;
                Cursor = Cursors.Default;
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnBrowse_Click(object sender, EventArgs e)
        {
            FolderBrowserDialog folderBrowse = new FolderBrowserDialog();
            folderBrowse.Description = "Chọn thư mục lưu chữ database";
            folderBrowse.ShowNewFolderButton = true;
            if (folderBrowse.ShowDialog() == DialogResult.OK)
            {
                if (Directory.Exists(folderBrowse.SelectedPath))
                {
                    tbxDatabaseFolder.Text = folderBrowse.SelectedPath;                                        
                }
                else
                {
                    MessageBox.Show("thư mục lựa chọn không tồn tại!", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
            }
        }

        private void btnCreateDatabase_Click(object sender, EventArgs e)
        {
            // Check Database Folder
            if (Directory.Exists(tbxDatabaseFolder.Text))
            {
                setting.DbFileDirectory = tbxDatabaseFolder.Text;
                setting.SaveSettings();
            }
            else
            {
                try
                {
                    // Create new folder
                    Directory.CreateDirectory(tbxDatabaseFolder.Text);
                    setting.DbFileDirectory = tbxDatabaseFolder.Text;
                    setting.SaveSettings();
                }
                catch (System.Exception ex)
                {                    
                    MessageBox.Show("Không tạo được thư mục chứa database", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);

                    return;
                }
            }

            ServerName = tbxServerName.Text;
            UserName = tbxUserName.Text;
            Password = tbxPassword.Text;

            importProgressDialog = new ProgressDlg();
            importProgressDialog.IsAutoRun = false;   

            thread = new Thread(DoSmt);
            thread.Start();

                     
            importProgressDialog.ShowDialog();   
        }
        private void DoSmt()
        {
            String fileName = Application.StartupPath + @"\SQLScript\QLBH.sql";
            if (!File.Exists(fileName))
            {
                importProgressDialog.SetError();                
                MessageBox.Show("Sql script file does not exist", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);                
                thread.Abort();
                thread = null;                
                return;
            }

            string sqlConnectionString = String.Empty;
            SqlConnection conn = new SqlConnection();
            FileInfo file = new FileInfo(fileName);

            try
            {
                importProgressDialog.POST(0, QLBH.StandardUI.ProgressDlg.eProgressType.PT_DEL_ODL_DB);                                
                if (DatabaseIsExist("QLBH"))
                {                    
                    sqlConnectionString = EntityDBConnectionHelper.Instance.GetMasterConnectionString(
                        authen, ServerName, UserName, Password);      
                    
                    conn = new SqlConnection(sqlConnectionString);
                    // Try to kill all connection before detach database;
                    SqlCommand killCommand = new SqlCommand("USE [master];ALTER DATABASE QLBH SET SINGLE_USER WITH ROLLBACK IMMEDIATE", conn);
                    killCommand.CommandTimeout = 30;
                    killCommand.CommandType = CommandType.Text;
                    conn.Open();
                    try
                    {
                        killCommand.ExecuteNonQuery();
                        conn.Close();
                    }
                    catch
                    {
                        conn.Close();
                    }
                    // Try to detach database from server;

                    SqlCommand cmd = new SqlCommand(@"sp_detach_db @dbname='QLBH', @keepfulltextindexfile='true'", conn);
                    cmd.CommandTimeout = 30;
                    cmd.CommandType = CommandType.Text;
                    conn.Open();
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch
                    {
                        // Do nothing;
                    }
                    conn.Close();
                }

                // And them remove file .mdf, .LDF;
                if (File.Exists(setting.DbFileDirectory + "\\QLBH.mdf"))
                {
                    System.IO.File.Delete(setting.DbFileDirectory + "\\QLBH.mdf");
                }
                if (File.Exists(setting.DbFileDirectory + "\\QLBH_log.ldf"))
                {
                    System.IO.File.Delete(setting.DbFileDirectory + "\\QLBH_log.ldf");
                }
            }
            catch (Exception ex)
            {
                importProgressDialog.SetError();
                MessageBox.Show(ex.StackTrace, "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);                
                thread.Abort();
                thread = null;                
                conn.Close();

                return;
            }

            try
            {

                if (!DatabaseIsExist("QLBH"))
                {
                    // Create database
                    
                    sqlConnectionString = EntityDBConnectionHelper.Instance.GetMasterConnectionString(
                        authen, ServerName, UserName, Password); 
                    
                    conn = new SqlConnection(sqlConnectionString);
                    conn.Open();
                    StreamReader reader = file.OpenText();
                    string script = reader.ReadToEnd();
                    reader.Close();

                    if (!String.IsNullOrEmpty(script))
                    {
                        script = script.Replace("QLBH.mdf", setting.DbFileDirectory + "\\QLBH.mdf");
                        script = script.Replace("QLBH_log.ldf", setting.DbFileDirectory + "\\QLBH_log.ldf");
                        StringBuilder addingScriptText = new StringBuilder();
                    }
                    Regex regex = new Regex("^\\s*GO\\s*$", RegexOptions.IgnoreCase | RegexOptions.Multiline);
                    string[] lines = regex.Split(script);
                    SqlCommand com = new SqlCommand(script, conn);
                    String command = "";
                    int index=0;
                    foreach (String line in lines)
                    {
                        if (line.Length > 0)
                        {
                            command += line + "\n";
                            com.CommandText = line;
                            try
                            {

                                com.ExecuteNonQuery();

                                importProgressDialog.POST(100*index/lines.Length,QLBH.StandardUI.ProgressDlg.eProgressType.PT_CREATEDB);                                
                            }
                            catch (System.Exception ex)
                            {
                                throw ex;

                            }

                        }
                        index++;
                    }
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                importProgressDialog.SetError();                
                if (thread.IsAlive)
                {
                    thread.Abort();
                } 
                thread = null;
                if (conn.State == ConnectionState.Open) conn.Close();
                file.OpenText().Close();
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }                                                                                    

            importProgressDialog.SetCompletedProgress();
            MessageBox.Show("Tạo CSDL thành công", "QLBH", MessageBoxButtons.OK, MessageBoxIcon.Information);
            if (conn.State == ConnectionState.Open) conn.Close();
            file.OpenText().Close();
            if (thread.IsAlive)
            {
                thread.Abort();
                thread = null;
            }

        }
        /// <summary>
        /// Check whether database is existed
        /// </summary>
        /// <param name="name">Name of the database</param>
        /// <returns>Return Boolean value</returns>
        private bool DatabaseIsExist(String name)
        {
            string sqlConnectionString = String.Empty;
            SqlConnection conn = new SqlConnection();
            
            sqlConnectionString = EntityDBConnectionHelper.Instance.GetMasterConnectionString(
                authen, ServerName, UserName, Password);
            
            conn = new SqlConnection(sqlConnectionString);
            SqlCommand cmdSelectDatabases = new SqlCommand("Select name from sys.sysdatabases", conn);
            conn.Open();
            SqlDataReader reader = cmdSelectDatabases.ExecuteReader(CommandBehavior.CloseConnection);
            while (reader.Read())
            {
                if (name.Equals(reader[0].ToString()))
                    return true;
            }
            conn.Close();
            return false;
        }

        private void btnOK_Click(object sender, EventArgs e)
        {
            this.Cursor = Cursors.WaitCursor;
            try
            {                                                
                setting.Authen = cmbAuthentication.SelectedIndex == 0 ? true : false;
                setting.ServerName = tbxServerName.Text;
                setting.UserName = tbxUserName.Text;
                setting.PassWord = tbxPassword.Text;
                setting.DbFileDirectory = tbxDatabaseFolder.Text;
                setting.SaveSettings();                                      
                this.Cursor = Cursors.Default;            
                this.DialogResult = DialogResult.OK;
                this.Close();
            }
            catch (System.Exception)
            {
                ShowMessage.Instance.ShowError("Không lưu được thông tin cấu hình, bạn nên chạy ứng dụng với quyền admin!");
                this.Cursor = Cursors.Default;            
            }                                                         
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            this.DialogResult = DialogResult.Cancel;
            this.Close();
        }

        private void cmbAuthentication_SelectedIndexChanged(object sender, EventArgs e)
        {
            authen = cmbAuthentication.SelectedIndex == 0;
            if (cmbAuthentication.SelectedIndex==0)
            {
                //window authentication
                tbxUserName.Enabled = false;
                tbxPassword.Enabled = false;
            }
            else
            {
                //window authentication
                tbxUserName.Enabled = true;
                tbxPassword.Enabled = true;
            }
        }

        private void FrmConfiguration_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Escape)
            {
                this.DialogResult = DialogResult.Cancel;
                this.Close();
            }
        }       
    }
}
